package service

import (
	"strings"
	"teacher-helper/dao"
	"teacher-helper/models"
	"teacher-helper/models/request"
	"teacher-helper/models/response"
	"time"
)

func FilterPartyStudent(filterInfo request.FilterInfo) (Scores []response.ScoreSummary, err error) {
	var stu_ids []int  // 不是中共党员且申请时间满一年
	var stu_ids2 []int	// 挂科学生
	var stu_ids3 []int  	// 成绩排名前40%
	var class_id_list []int
	var total int
	var semester_ids = strings.Split(filterInfo.SemesterIDS, "-")
	cur_date := time.Now().Format("2006-01-02")

	dao.DB.Model(&models.SysClass{}).Joins("left join sys_grades on sys_classes.grade_id = sys_grades.id").Where("sys_grades.name = ?", filterInfo.GradeName).Pluck("sys_classes.id", &class_id_list)
	dao.DB.Model(&models.SysStudent{}).Where("class_id in (?)", class_id_list).Count(&total)
	top_count := int(float64(total) * filterInfo.Rank)
	// 年龄大于19岁、申请入党满一年和不是党员
	dao.DB.Model(&models.SysPolitical{}).Joins("left join sys_students on sys_students.stu_id = sys_politicals.stu_id").Where("sys_students.class_id in (?) and DATEDIFF(?, sys_students.birthday) > 6935 and DATEDIFF(?, sys_politicals.application_party_time) > 365 and sys_students.political != '中共党员'", class_id_list, cur_date, cur_date).Pluck("sys_students.stu_id", &stu_ids)
	// 是否挂科
	dao.DB.Model(&models.SysScore{}).Where("score < 60 and semester_id in (?) and class_id in (?)", semester_ids, class_id_list).Pluck("stu_id", &stu_ids2)
	// 排名前40%
	dao.DB.Model(&models.SysScore{}).Limit(top_count).Where("semester_id in (?) and class_id in (?) and stu_id not in (?)", semester_ids, class_id_list, stu_ids2).Group("stu_id").Order("sum(score) desc").Pluck("stu_id", &stu_ids3)
	// 综合筛选最后结果
	err = dao.DB.Model(&models.SysScore{}).Select("stu_id, sys_scores.name, sum(score) as total, TRUNCATE(avg(score),2) as mean, sys_classes.name as class_name,sys_semesters.school_year as school_year").Joins("left join sys_classes on sys_scores.class_id = sys_classes.id").Joins("LEFT JOIN sys_semesters on sys_scores.semester_id = sys_semesters.id").Where("semester_id in (?) and class_id in (?) and stu_id in (?) and stu_id in (?)", semester_ids, class_id_list, stu_ids, stu_ids3).Group("stu_id").Order("total desc").Scan(&Scores).Error
	return
}
